from numpy import info, number
import pandas as pd
import re
import pymysql
import datetime


bwlabel=[]
bwlabel_bjtj=[]
sql_early=[]

def bjjl(tt):
    print('123')   #  r'C:\Users\gpf\Desktop\报警统计.xls'
    bjjl=pd.read_excel(tt,header=3,skiprows=-2,dtype={'名称':str,'报警起始时间':str})
    bjjl=bjjl[:-2]
    # print(bjjl)
    for __i in bjjl.index:
        if bjjl['序号'].at[__i] != '0' and (re.findall(r'地点.*?(甲烷)',bjjl['序号'].at[__i]))==['甲烷'] and float((re.findall(r'最大值/时刻：(.*?)/',bjjl['序号'].at[__i]))[0])>1.48:
            for __j in range(1,int((re.findall(r'累计报警次数：(.*?)次',bjjl['序号'].at[__i]))[0])+1):
                if '洗煤厂' not in ((re.findall(r'(洗煤厂)',bjjl['断电区域'].at[__i+__j]))):
                    __aa={
                    'sensor_no':(re.findall(r'编号: (.*?) 地',bjjl['序号'].at[__i]))[0],
                    'sensor_no_adress':(re.findall(r'地点：(.*?)甲烷',bjjl['序号'].at[__i]))[0]    ,     
                    'date':bjjl['报警起始时间'].at[__i+__j].split(' ')[0],
                    'start_time':bjjl['报警起始时间'].at[__i+__j].split(' ')[1],
                    'end_time':bjjl['报警结束时间'].at[__i+__j].split(' ')[1],
                    'with_time':bjjl['持续时间'].at[__i+__j].rsplit(':',1)[0]
                    }
                    bwlabel.append(__aa)
                    __aa={}

def gztj(tt):
    gztj=pd.read_excel(tt,header=3,skiprows=-2)
    gztj=gztj[:-2]
    for __ii in gztj.index:
        if gztj['名称'].at[__ii] not in range(0,1000) and (re.findall(r'地点.*?(甲烷)',gztj['序号'].at[__ii]))==['甲烷']:
            for __jj in range(1,int((re.findall(r'累计故障次数：(.*?)次',gztj['序号'].at[__ii]))[0])+1):
                if str(gztj['故障类型'].at[__ii+__jj])=='断线':
                    __bb={
                    'sensor_no_gztj':(re.findall(r'编号: (.*?) 地',gztj['序号'].at[__ii]))[0], 
                    'sensor_no_adress_gztj':(re.findall(r'地点: (.*?)甲烷',gztj['序号'].at[__ii]))[0],
                    'start_time_gztj':gztj['故障起始时间'].at[__ii+__jj].split(' ')[1],
                    'date_gztj':gztj['故障起始时间'].at[__ii+__jj].split(' ')[0],
                    'end_time_gztj':gztj['故障起始时间'].at[__ii+__jj].split(' ')[1],
                    }
                    bwlabel_bjtj.append(__bb)
                    __bb={}
# print(bwlabel)
# print(bwlabel_bjtj)
# 合并报警数据表和故障统计表
def add_data(cc=None):
    for _aa in bwlabel:
        for _bb in bwlabel_bjtj:
            if _aa['sensor_no']== _bb['sensor_no_gztj']:
                __cc={**_aa,**_bb}
                sql_early.append(__cc)

# def save_excel():
    # print(sql_early)
    # sql_early.pd.to_txt('c:/123/555.txt')
    # for i in sql_early:
    #     i.to_excel('c:/123/555.xls')

# 数据库部分
def add_sql():
    db=pymysql.connect(host='localhost',user='root',
        password='5947109',database='zdhbb',port=3306,charset='utf8')
    #
    with db.cursor() as cursor:    #   https://blog.csdn.net/tzyyy1/article/details/93510663

        for i in sql_early:
            # xxx1=(i['sensor_no'])
            # xxx2=i['sensor_no_adress']
            # print(xxx1)
            
            cursor.execute('INSERT INTO `zdhbb` VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'%(i['sensor_no'],i['sensor_no_adress'],i['date'],i['start_time'],i['end_time'],i['with_time'] ,i['sensor_no_gztj'],i['sensor_no_adress_gztj'],i['start_time_gztj'],i['date_gztj'],i['end_time_gztj']))
            db.commit()
            infos=list(cursor.fetchall())
            # print(infos)

def select_data_sql():
    db=pymysql.connect(host='localhost',user='root',password='5947109',database='zdhbb',port=3306,charset='utf8')
    with db.cursor() as cursor:  
        date_input='%'+input('请输入要查询日期')+'%'
        sql=(("SELECT `id`, `地址`, `更换日期`, `报警开始时间`, `报警结束时间`, `报警持续时间`, `断电id`, `断电地址`, `断电开始时间`, `断电日期`, `断电结束时间` FROM `zdhbb`.`zdhbb` WHERE `更换日期` LIKE '%s'")%(date_input))
        cursor.execute(sql)
        db.commit()
        infos=list(cursor.fetchall())
        return infos
        # for i in infos:
        #     print('----------')
        #     print(list(i))
    return infos         
        # print(datetime.date(2021, 4, 22))



bjjl(r"C:\Users\gpf\Desktop\报警统计.xls")
gztj(r"C:\Users\gpf\Desktop\故障统计.xls")
add_data()
# save_excel()
# print(sql_early)
# add_sql()
select_sql()
# print(str(datetime.timedelta(seconds=75075)))
print(infos)